
set hive.merge.mapfiles = true;
set hive.merge.mapredfiles = true;
set hive.merge.size.per.task = 256000000;
set hive.merge.smallfiles.avgsize=16000000;

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=10000;
set hive.exec.max.created.files=10000;

-- 不想要这段代码
-- insert overwrite table bjshell.TBL_ORDERDETAILS_CD partition(dt)
-- select  ORDERDETAILS_ID    ,
-- VERSION            ,
-- NOWDATE            ,
-- VIRTUALGOODSID     ,
-- RETURNCODE         ,
-- LITRE              ,
-- if(STATIONCODE = '58010','58008',STATIONCODE)       ,
-- COUPONAMOUNT       ,
-- REQUESTID          ,
-- ORDERNUM           ,
-- REALAMOUNT         ,
-- OILCODE            ,
-- ORDER_DEAL         ,
-- DISCOUNTAMOUNT     ,
-- CARDUSERID         ,
-- AMOUNT             ,
-- OILPRICE           ,
-- TERMNUM            ,
-- SENDTIME           ,
-- INVOICETITLE       ,
-- MEMBERNO           ,
-- VIRTUALGOODSIDS    ,
-- CUSTOMER_ID        ,
-- create_shijian     ,
-- PPSORDERNUMBER     ,
-- GOODSAMOUNT        ,
-- FUELCOUPONAMOUNT   ,
-- GOODCOUPONAMOUNT   ,
-- GOODSCOUPONAMOUNT  ,
-- UPDATEREALAMOUNT   ,
-- FULECOUPONAMOUNT   ,
-- FULEAMOUNT         ,
-- CARLICENSE         ,
-- OILGUNNO           ,
-- ORDER_STATE        ,
-- EPSCODE,dt from bjshell.TBL_ORDERDETAILS_CD where dt in ${dt};
--
-- insert overwrite table bjshell.TBL_ORDERDETAILS partition(dt)
-- select ORDERDETAILS_ID    ,
-- VERSION            ,
-- NOWDATE            ,
-- VIRTUALGOODSID     ,
-- RETURNCODE         ,
-- LITRE              ,
-- if(STATIONCODE = '58010','58008',STATIONCODE)       ,
-- COUPONAMOUNT       ,
-- REQUESTID          ,
-- ORDERNUM           ,
-- REALAMOUNT         ,
-- OILCODE            ,
-- ORDER_DEAL         ,
-- DISCOUNTAMOUNT     ,
-- CARDUSERID         ,
-- AMOUNT             ,
-- OILPRICE           ,
-- TERMNUM            ,
-- SENDTIME           ,
-- INVOICETITLE       ,
-- MEMBERNO           ,
-- VIRTUALGOODSIDS    ,
-- CUSTOMER_ID        ,
-- create_shijian     ,
-- PPSORDERNUMBER     ,
-- GOODSAMOUNT        ,
-- FUELCOUPONAMOUNT   ,
-- GOODCOUPONAMOUNT   ,
-- GOODSCOUPONAMOUNT  ,
-- UPDATEREALAMOUNT   ,
-- FULECOUPONAMOUNT   ,
-- FULEAMOUNT         ,
-- CARLICENSE         ,
-- OILGUNNO           ,
-- ORDER_STATE        ,
-- EPSCODE,dt  from bjshell.TBL_ORDERDETAILS where dt in ${dt}


insert overwrite table bjshell.TBL_ORDERDETAILS_ALL partition(dt)
select ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58010','58008',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE,substr(SENDTIME,1,10) dt  from TBL_ORDERDETAILS where substr(SENDTIME,1,10) in ${dt} union all
select  ORDERDETAILS_ID    ,
VERSION            ,
NOWDATE            ,
VIRTUALGOODSID     ,
RETURNCODE         ,
LITRE              ,
if(STATIONCODE = '58010','58008',STATIONCODE)       ,
COUPONAMOUNT       ,
REQUESTID          ,
ORDERNUM           ,
REALAMOUNT         ,
OILCODE            ,
ORDER_DEAL         ,
DISCOUNTAMOUNT     ,
CARDUSERID         ,
AMOUNT             ,
OILPRICE           ,
TERMNUM            ,
SENDTIME           ,
INVOICETITLE       ,
MEMBERNO           ,
VIRTUALGOODSIDS    ,
CUSTOMER_ID        ,
create_shijian     ,
PPSORDERNUMBER     ,
GOODSAMOUNT        ,
FUELCOUPONAMOUNT   ,
GOODCOUPONAMOUNT   ,
GOODSCOUPONAMOUNT  ,
UPDATEREALAMOUNT   ,
FULECOUPONAMOUNT   ,
FULEAMOUNT         ,
CARLICENSE         ,
OILGUNNO           ,
ORDER_STATE        ,
EPSCODE,substr(SENDTIME,1,10) dt from TBL_ORDERDETAILS_CD where substr(SENDTIME,1,10) in ${dt};




insert overwrite  table bjshell.transactions_vip   partition(dt)
select B.CARDUSERID,A.*
from
 ( select
            transaction_id,
            cashier_id,
            sub_account_id,
            pos_id,
            transaction_number,
            transaction_date,
            period_id,
            tax_total,
            sale_total,
            bir_receipt_type,
            bir_trans_number,
            refunded,
            alt_transaction_number,
            cdraw_period_id,
            receipt_number,
            transaction_start_dt,
            award_total,
            redemption_total,
            refund_total,
            discount_total,
            refunded_date,
            is_refund,
            disc_as_mop_total,
            transaction_db_date,
            create_shijian,
            station_id,dt from bjshell.`transactions_parquet` where dt in ${dt} ) as A
join (select CARDUSERID,ORDERNUM,STATIONCODE,AMOUNT,ORDER_DEAL,dt from bjshell.TBL_ORDERDETAILS_ALL where dt in ${dt} ) as B
on A.transaction_number = B.ORDERNUM and A.station_id = B.STATIONCODE and A.sale_total = B.AMOUNT/100 AND ORDER_DEAL = 4 and A.dt = B.dt;

-- cs_dt
INSERT OVERWRITE TABLE bjshell.transaction_items_mop_vip partition(dt)
select A.carduser_id,B.*,dt
from
(select carduser_id,transaction_id,station_id,dt from bjshell.transactions_vip where dt in ${dt}) as A
JOIN transaction_items_mop as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id;

INSERT OVERWRITE TABLE bjshell.transaction_items_notoil_vip  partition(dt)
select A.carduser_id,B.*,dt
from (select carduser_id,transaction_id,station_id,dt from bjshell.transactions_vip where dt in ${dt}) as A
JOIN transaction_items_notoil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id;

INSERT OVERWRITE TABLE bjshell.transaction_items_oil_vip  partition(dt)
select A.carduser_id,B.*,dt
from (select carduser_id,transaction_id,station_id,dt from bjshell.transactions_vip where dt in ${dt}) as A
JOIN transaction_items_oil as B
ON A.transaction_id = B.transaction_id and A.station_id = B.station_id;

-- 到这了
insert overwrite table bjshell.res_oil_vip  partition(dt)
select substr(SENDTIME,1,16),STATIONCODE,OILCODE,COUNT(*),SUM(LITRE)/1000,SUM(REALAMOUNT)/100,dt
FROM bjshell.TBL_ORDERDETAILS_ALL WHERE LITRE != 0 and ORDER_DEAL = 4 and dt in ${dt} GROUP BY substr(SENDTIME,1,16),dt,STATIONCODE,OILCODE;

insert overwrite table bjshell.res_notoil_vip partition(dt)
select substr(SENDTIME,1,16),STATIONCODE,COUNT(*),SUM(REALAMOUNT)/100,dt
FROM bjshell.TBL_ORDERDETAILS_ALL WHERE LITRE = 0 and ORDER_DEAL = 4 and dt in ${dt} GROUP BY substr(SENDTIME,1,16),dt,STATIONCODE,OILCODE;

insert overwrite table bjshell.res_notoilDepartment_vip partition(dt)
select substr(X.transaction_date,1,16),X.station_id,X.dept_name,count(X.department_id),sum(X.item_value),dt
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Y.department_id,C.dept_name,dt
from (select transaction_date,transaction_id,station_id,dt from bjshell.transactions_vip where dt in ${dt}) as Z
join transaction_items_notoil_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
join products as B
on Y.product_code = B.product_code and  B.station_id = 50006
join departments as C
on C.station_id = 50006 and B.department_id = C.department_id
)AS X
group by
substr(X.transaction_date,1,16),X.station_id,X.dept_name,dt;
insert overwrite table bjshell.mid_notoilTop_vip  partition(dt)
select substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code,sum(X.item_quantity),sum(X.item_value),dt
from
(select Y.transaction_id,Z.transaction_date,Y.station_id,Y.item_description,Y.item_value,Y.product_code,Y.item_quantity,dt
from (select transaction_date,transaction_id,station_id,dt from bjshell.transactions_vip where dt in ${dt}) as Z
join transaction_items_notoil_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id )AS X
group by substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code,dt;


------------------------------------------------------------------------------------------
insert overwrite table bjshell.res_notoilTop_vip  partition(dt)
select minutes,stationID,top10,productCode,number,money,rank,dt from
(select minutes,stationID,top10,productCode,number,money,
row_number() over (partition by minutes,stationID order by number desc)AS rank,dt from bjshell.mid_notoilTop_vip where dt in ${dt}
)b
where b.rank<=10;

insert OVERWRITE table bjshell.mid_lube_vip  partition(dt)
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value,d.oillitre,dt from 
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,trim(A.item_description)as oillitre,C.dt
from bjshell.transaction_items_notoil_vip as A
join products as B
on A.product_code = B.product_code and B.station_id = 50006
join (select transaction_date,transaction_id,dt from bjshell.transactions_vip where dt in ${dt}) as C
on C.transaction_id = A.transaction_id
)d where d.department_id in (548,64);

insert overwrite table bjshell.res_lube_vip partition(dt)
select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(substr(oillitre,-2,1)),sum(itemvalue),dt
from bjshell.mid_lube_vip where dt in ${dt}
group by substr(minutes,1,16),stationID,dt;


insert overwrite table bjshell.mid_notoilRate_vip partition(dt)
select substr(transaction_date,1,13),station_id,count(*),dt
from bjshell.transactions_vip where dt in ${dt}
group by substr(transaction_date,1,13),station_id,dt;

insert overwrite table bjshell.res_notoilRate_vip partition(dt)
select A.houra,A.stationID,A.numbera,B.numberb,dt
FROM
(select SUBSTR(minutes,1,13)AS houra,stationID,sum(notoilNumber)AS numbera,dt
from bjshell.res_notoil_vip where dt in ${dt}
group by SUBSTR(minutes,1,13),stationID,dt) AS A
JOIN
(select substr(minutes,1,13)AS hourb,stationID,sum(tradeNumber)AS numberb
from bjshell.mid_notoilRate_vip where dt in ${dt}
group by substr(minutes,1,13),stationID) AS B
ON A.houra=B.hourb and A.stationID=B.stationID;





insert overwrite table bjshell.res_mop_vip partition(dt)
select substr(X.transaction_date,1,13),X.station_id,X.mop_name,count(X.item_quantity),sum(X.item_value),dt
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity,dt
from 
(select transaction_date,transaction_id,station_id,dt from bjshell.transactions_vip where dt in ${dt}) as Z 
join transaction_items_mop_vip as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id and Y.item_value > 0 )AS X
group by substr(X.transaction_date,1,13),X.station_id,X.mop_name,dt;

insert overwrite table bjshell.res_HHTIPT_vip partition(dt)
select B.substrb,B.station_id,A.count,A.sum,B.count-A.count,B.sum-A.sum,B.count,B.sum,dt
from
(select substr(transaction_date,1,13)as substrb,station_id,count(transaction_id)as count,sum(sale_total)as sum,dt
from bjshell.transactions_vip where dt in ${dt}
group by substr(transaction_date,1,13),station_id,dt)as B
join
(select substr(Z.transaction_date,1,13)as substra,Z.station_id,count(Z.transaction_id)as count,sum(Z.sale_total)as sum
from
(select transaction_id,transaction_date,station_id,sale_total,cashier_id from bjshell.transactions_vip where dt in ${dt}) AS Z
JOIN cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT'
group by substr(Z.transaction_date,1,13),Z.station_id) as A
on A.substra=B.substrb AND A.station_id=B.station_id;

set hive.auto.convert.join=false;

insert overwrite table bjshell.res_mopipt_vip partition(dt)
select substr(B.transaction_date,1,13),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value),B.dt
from bjshell.transaction_items_mop_vip as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date,dt
from
(select transaction_date,transaction_id,station_id,cashier_id,dt from bjshell.transactions_vip where dt in ${dt})  AS Z
JOIN cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name != 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id and A.item_value > 0
group by substr(B.transaction_date,1,13),A.station_id,A.mop_name,B.dt;

set hive.auto.convert.join=false;-----



insert overwrite table bjshell.res_mophht_vip partition(dt)
select substr(B.transaction_date,1,13),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value),B.dt
from bjshell.transaction_items_mop_vip as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date,dt
from
(select transaction_date,transaction_id,station_id,cashier_id,dt from bjshell.transactions_vip where dt in ${dt})  AS Z
JOIN cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id and A.item_value > 0
group by substr(B.transaction_date,1,13),A.station_id,A.mop_name,B.dt;

insert OVERWRITE table bjshell.mid_exceptLube_vip partition(dt)
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value,dt from 
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,dt
from transaction_items_notoil_vip as A
join products as B
on A.product_code = B.product_code and B.station_id = 50006
join 
(select transaction_date,transaction_id,station_id,dt from bjshell.transactions_parquet where dt in ${dt}) as C
on C.transaction_id = A.transaction_id and C.station_id = A.station_id 
)d where d.department_id not in (548,64);

insert overwrite table bjshell.res_exceptLube_vip partition(dt)
select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(itemvalue),dt
from bjshell.mid_exceptLube_vip where dt in ${dt}
group by substr(minutes,1,16),stationID,dt;